package com.mingke.java.task;


import com.mingke.java.params.AdvancedOperationParams;
import com.mingke.java.params.OperationBaseParams;
import com.mingke.java.params.BackupReturnValue;
import com.mingke.java.params.SimpleOperationParams;
import com.mingke.java.utils.DataSourceUtils;
import com.mingke.java.utils.OperationMode;

import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Optional;
import java.util.concurrent.*;

/**
 * 数据备份任务实体类
 */
public class DataOperationTask {

    private static ThreadPoolExecutor executor  = new ThreadPoolExecutor(10, 12, 60L, TimeUnit.SECONDS, new LinkedBlockingQueue<Runnable>(16));

    /**
     * 简单备份
     * @param params 参数
     * @return 执行结果
     */
    public void simpleOperation(SimpleOperationParams params) {
        //读取目标数据库
        DataSourceUtils sourceUtils = new DataSourceUtils();
        //设置目标库连接信息
        sourceUtils.initSimpleConnection(params.getSourceDBURL(), params.getSourceUser(), params.getSourcePassword());


        //设置查询SQL
        String SQL = "";
        if(params.getType()==1) {
            //按时间
            String selectRawSQL = generateSQLAboutTime(params.getTableName(), params.getColumnName(), params.getMode());
            SQL = sourceUtils.generateSQL(selectRawSQL, params.getDate());
        }else {
            //按条数
            String selectRawSQL = generateSQLAboutRecent(params.getTableName(), params.getMode());
            SQL = sourceUtils.generateSQL(selectRawSQL, params.getNum());
        }

        //执行操作
        if(params.getMode() == 1) {
            doBackup(SQL, params);
        }else if(params.getMode() == 2) {
            doDestroy(SQL, params);
        }

        //关闭数据库连接
        sourceUtils.close();
    }

    /**
     * 高级备份
     * @param params 参数
     * @return 执行结果
     */
    public void advancedOperation(AdvancedOperationParams params) {
        //设置查询SQL
        String SQL = generateAdvancedSQL(params.getTableName(), params.getCondition(), OperationMode.BackupMode.getMode());

        //执行操作
        if(params.getMode() == 1) {
            doBackup(SQL, params);
        }else if(params.getMode() == 2) {
            doDestroy(SQL, params);
        }
    }

    private void doBackup(String sql, OperationBaseParams params) {
        //执行备份
        if(params.getOperationStrategy()==1) {
            //数据库备份
            doDataBaseBackup(sql, params);
        } else if (params.getOperationStrategy()==2) {
            //磁盘备份
            try {
                doDiskBackup(sql, params);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        } else {
            System.out.println("参数错误");
        }
    }

    private void doDestroy(String sql, OperationBaseParams params) {
        if(params.getOperationStrategy()==1) {
            doPermanentDestroy(sql, params);
        }else if(params.getOperationStrategy()==2) {
            String selectSQL = sql.replace("DELETE", "SELECT *");
            doRecycleDestroy(sql, selectSQL, params);
        }else {
            System.out.println("参数异常");
        }
    }


    private BackupReturnValue getSelectResultSet(String sql, OperationBaseParams params) {
        BackupReturnValue result = new BackupReturnValue();
        //读取源数据库
        DataSourceUtils sourceUtils = new DataSourceUtils();
        //设置数据库连接信息
        sourceUtils.initSimpleConnection(params.getSourceDBURL(), params.getSourceUser(), params.getSourcePassword());
        //执行查询SQL
        ResultSet resultSet = sourceUtils.doSQL(sql);
        result.setResultSet(resultSet);
        if(resultSet != null) {
            try {
                //解析ResultSet中数据的结构信息
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                //获取列的个数
                int columnCount = resultSetMetaData.getColumnCount();
                result.setColumnNum(columnCount);
                //获取指定下标列所属于的表的表名，因为此处的SQL执行可能涉及到联表查询，所以这里有这样的设计
                String oldTableName = resultSetMetaData.getTableName(1);
                String backupTableName = oldTableName + "_" + getSuffix();

                //同步环境下用StringBuffer，多线程并发用StringBuilder
                //拼接原始模版SQL
                StringBuffer headBuffer = new StringBuffer();
                StringBuffer tailBuffer = new StringBuffer();
                headBuffer.append("INSERT INTO ").append(backupTableName).append(" ");
                tailBuffer.append(" VALUES (");

                headBuffer.append('(');
                //获取列名
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    headBuffer.append(columnName);
                    tailBuffer.append('?');
                    if (i != columnCount) {
                        headBuffer.append(", ");
                        tailBuffer.append(", ");
                    }
                }
                headBuffer.append(')');
                tailBuffer.append(")");
                headBuffer.append(tailBuffer);
                result.setInsertRawSQL(headBuffer.toString());

                //获取到创建数据表的SQL
                String createSQL = "";
                String showSQL = "show create table " + oldTableName;
                ResultSet createResultSet = sourceUtils.doSQL(showSQL);
                while (createResultSet.next()) {
                    createSQL = Optional.ofNullable(createResultSet.getString("Create Table")).orElse("创建表SQL未获取到，请检查表名是否有误");
                }
                //替换原有表名，注意此处仅需要替换第一个匹配到的也就是原来的表名
                createSQL = createSQL.replaceFirst(oldTableName, backupTableName)+";";

                result.setDataSourceUtils(sourceUtils);
                result.setCreateSQL(createSQL);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


        return result;
    }

    private void doDataBaseBackup(String sql, OperationBaseParams params) {
        //读取目标数据库
        DataSourceUtils targetUtils = new DataSourceUtils();
        //设置目标库连接信息
        targetUtils.initSimpleConnection(params.getTargetDBURL(), params.getTargetUser(), params.getTargetPassword());

        //获取查询返回值
        BackupReturnValue returnValue = getSelectResultSet(sql, params);

        //执行创建新表的SQL，这样执行速度会很慢，可以考虑另外开一个线程单独执行
        targetUtils.doSQL(returnValue.getCreateSQL());
        ExecutorService executorService = Executors.newFixedThreadPool(10);

        long begin = System.currentTimeMillis();
        long end = System.currentTimeMillis();
        try {
            int index = 0;
            ResultSetMetaData metaData = returnValue.getResultSet().getMetaData();
            int columnNum = metaData.getColumnCount();
            //初始化插入模板SQL
            targetUtils.initBatchExecutor(returnValue.getInsertRawSQL());
            while(returnValue.getResultSet().next()) {
                Object[] objs = new Object[columnNum];
                for(int i = 1; i <= columnNum; i++) {
                    objs[i-1] = returnValue.getResultSet().getObject(i);
                }
                targetUtils.fillBatchSQLParams(objs);

                if(index % DataSourceUtils.fetchSize == 0) {
                    executorService.submit(() -> targetUtils.doBatchSQL());
                }
                index++;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        executorService.submit(() -> targetUtils.doBatchSQL());

        targetUtils.close();
        end = System.currentTimeMillis();
        System.out.println((end-begin)+"ms");
    }


    private void doDiskBackup(String sql, OperationBaseParams params) throws IOException {
        //读取数据库
        DataSourceUtils sourceUtils = new DataSourceUtils();
        //设置数据库连接信息
        sourceUtils.initSimpleConnection(params.getSourceDBURL(), params.getSourceUser(), params.getSourcePassword());
        //初始化文件写入缓冲区
        BufferedWriter bufferedWriter = Files.newBufferedWriter(Paths.get(params.getBackupPath()), StandardCharsets.UTF_8);
        //获取查询返回值
        BackupReturnValue returnValue = getSelectResultSet(sql, params);

        try {

            //写入头部说明信息
            bufferedWriter.write("/*");
            bufferedWriter.write("\n");
            bufferedWriter.write("  SQL备份时间: "+LocalDateTime.now());
            bufferedWriter.write("\n");
            bufferedWriter.write("  SQL备份原数据表名: "+ params.getTableName());
            bufferedWriter.write("\n");
            bufferedWriter.write("  SQL备份目标数据表名: "+ params.getTableName());
            bufferedWriter.write("\n");
            bufferedWriter.write("  Create By DataColl V1.0");
            bufferedWriter.write("\n");
            bufferedWriter.write("*/");
            bufferedWriter.write("\n");

            //写入创建数据表的SQL
            bufferedWriter.write(returnValue.getCreateSQL());
            bufferedWriter.write("\n\n\n\n");

            //获取列值，拼接可执行版SQL
            while (returnValue.getResultSet().next()) {
                Object objs[] = new Object[returnValue.getColumnNum()];
                //将列值取出直接放到Object数组中
                for (int i = 1; i <= returnValue.getColumnNum(); i++) {
                    objs[i - 1] = returnValue.getResultSet().getObject(i);
                }
                //使用SQL生成器生成可执行SQL
                String SQL = sourceUtils.generateSQL(returnValue.getInsertRawSQL(), objs);

                //将SQL写入到文件中
                bufferedWriter.write(SQL);
                bufferedWriter.newLine();
            }

            //flush一下，把所有缓冲区的信息写入到磁盘文件中
            bufferedWriter.flush();
            //关闭一下资源
            returnValue.getDataSourceUtils().close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭一下资源
            sourceUtils.close();
            bufferedWriter.close();
        }
    }

    private void doPermanentDestroy(String deletedSql, OperationBaseParams params) {
        //读取数据库
        DataSourceUtils sourceUtils = new DataSourceUtils();
        //设置数据库连接信息
        sourceUtils.initSimpleConnection(params.getSourceDBURL(), params.getSourceUser(), params.getSourcePassword());

        //执行SQL删除数据
        sourceUtils.doSQL(deletedSql);
    }

    private void doRecycleDestroy(String deletedSql, String selectSQL, OperationBaseParams params) {
        //将原有数据备份到另一数据库
        doDataBaseBackup(selectSQL, params);

        //删除原有数据
        doPermanentDestroy(deletedSql, params);
    }

    /**
     * 生成查询指定时间点以后所有记录的模板SQL
     * @param tableName 查询的表名
     * @param columnName 时间类型列名
     * @return
     * SELECT * FROM sys_user WHERE create_time > '2020-1-1'
     */
    private String generateSQLAboutTime(String tableName, String columnName, int category) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" WHERE ");
        stringBuffer.append(columnName);
        stringBuffer.append(" > ?");
        return stringBuffer.toString();
    }

    private String generateBatchSQLAboutTime(String tableName, String columnName, int category, int index, int sum) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" WHERE ");
        stringBuffer.append(columnName);
        stringBuffer.append(" > ?");
        stringBuffer.append(" limit "+index);
        stringBuffer.append(", "+sum);
        return stringBuffer.toString();
    }

    /**
     * 生成查询最近N条数据的模板SQL
     * @param tableName 查询的表名
     * @return
     * SELECT * FROM sys_user ORDER BY create_time DESC LIMIT 5
     */
    private String generateSQLAboutRecent(String tableName, int category) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" ORDER BY create_time DESC LIMIT ?");
        return stringBuffer.toString();
    }

    private String generateBatchSQLAboutRecent(String tableName, int category, int index, int sum) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" ORDER BY create_time DESC");
        stringBuffer.append(" limit "+index);
        stringBuffer.append(", "+sum);
        return stringBuffer.toString();
    }

    /**
     * 生成高级模式下查询的模板SQL
     * @param tableName 表名
     * @param condition 查询条件
     * @return
     */
    private String generateAdvancedSQL(String tableName, String condition, int category) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" WHERE ");
        stringBuffer.append(condition);
        return stringBuffer.toString();
    }

    private String generateBatchAdvancedSQL(String tableName, String condition, int category, int index, int sum) {
        StringBuffer stringBuffer = new StringBuffer();
        if(category == 1) { //查询
            stringBuffer.append("SELECT *");
        }else if(category == 2) { //删除
            stringBuffer.append("DELETE");
        }
        stringBuffer.append(" FROM ");
        stringBuffer.append(tableName);
        stringBuffer.append(" WHERE ");
        stringBuffer.append(condition);
        stringBuffer.append(" limit "+index);
        stringBuffer.append(", "+sum);
        return stringBuffer.toString();
    }

    /**
     * 生成时间戳后缀的方法
     * @return
     */
    private String getSuffix() {
        Timestamp timestamp = Timestamp.valueOf(LocalDateTime.now());
        SimpleDateFormat format = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
        return format.format(new Date(timestamp.getTime()));
    }
}
